Creating Reports from Excel and Access

This section describes the Add-Ins for Microsoft Excel and Microsoft Access. It explains how the Crystal Report Wizard is installed and how you create reports from an Access table or query and an Excel spreadsheet.


Overview of the Crystal Reports Add-Ins

Crystal Reports takes advantage of Microsoft’s Add-In technology by offering a report creation Wizard you can use with Microsoft Excel and Microsoft Access. The Crystal Report Wizard offers familiar report design capabilities directly within your Microsoft Office applications—you can create a Crystal report from your data without leaving Excel or Access.

The method for working with the Add-Ins is similar in both Excel and Access:

•	open Microsoft Excel or Microsoft Access (the Add-Ins are automatically installed when you install Crystal Reports)

•	select a spreadsheet (or selected cells), table, or query and launch the Wizard

•	use the creation Wizard’s familiar screen layout to create a report to your specifications.

Note:  The Add-Ins work with only the Office 97 and Office 2000 versions of Excel and Access.


About the Microsoft Excel Add-In

Choose an entire data range in a spreadsheet, or select certain cells to create a Crystal report. The report you create is linked to your Excel spreadsheet and can be refreshed to show changes made to the spreadsheet data.

Note:  Excel Add-In reports are updated when you refresh their data in Crystal Reports if you have:

•	added a row to the selected spreadsheet range (but not pre-appended or appended a row)

•	deleted a row from the selected spreadsheet range

•	modified a cell within the selected spreadsheet range.

After you create a report using the Wizard, you can preview it using the default ActiveX viewer or you can launch Crystal Reports or Crystal Analysis to modify it.


About the Microsoft Access Add-In

Choose a table or query to create a Crystal report. The report you create is linked to your Access table or query and can be refreshed to show changes made to the database.

Note:

•	Access Add-In reports will not reflect database changes until you close the modified table or query in Access and refresh the report data in Crystal Reports.

•	Access Add-In reports must be based on one table or query (but not a query with input parameters).

After you create a report using the Wizard, you can preview it using the default ActiveX viewer or you can launch Crystal Reports or Crystal Analysis to modify it.


Working with the Microsoft Excel Add-In

When you install Crystal Reports 8, the Excel Add-In is added automatically. A Crystal Report Wizard menu option is added to the Tools menu in Excel and a button is added to the Standard toolbar.

You can use the Crystal Report Wizard to create a report anytime you use Excel. The option remains available until you uncheck Crystal Report Wizard in the Add-Ins dialog box (under the Tools menu in Excel).

Note: The Excel Add-In file is placed by default in the \Program Files\Seagate Software\Report Designer Component directory.


Using the Report Creation Wizard to create a report in Excel
Using the Crystal Report Wizard to create a report in Excel

The Crystal Report Wizard in Excel offers familiar Crystal layout and selection screens to help you create a report from your spreadsheet. You can select a range of cells, or an entire data range in a spreadsheet to use as your report content.

Note:  When you create a report from an Excel spreadsheet, Crystal Reports creates a database file (.mdb). This file, the Excel spreadsheet file (.xls), and the report file itself (.rpt) cannot be moved or you’ll lose the links between them and won’t be able to refresh your report data later.

To create a report in Excel using the Report Creation Wizard

1	On the Tools menu in Excel, choose Crystal Report Wizard.

If your spreadsheet has unsaved data, Excel warns you to save before running the Wizard.

2	Type a data range in the cell range box, or click its button to choose a range directly from the spreadsheet.

Clicking the button minimizes the Wizard so you can click-drag to select a data range. To return to the Wizard, click the button on the minimized Wizard.

Note:  To select an entire data range in the spreadsheet, click “Expand the selected range automatically.”

3	With a data range selected, click Create Crystal Report.

The Crystal Report Wizard appears.

Note:  As you design your report, the program creates a database file (.mdb). The database file, the spreadsheet file (.xls), and the resulting report file (.rpt) cannot be moved or you won’t be able to refresh your report data later.

4	Add the database fields you want to appear in your report to the Fields to Display list.

The arrow buttons on this dialog box enable you to move fields from one list to the other. Single arrows move only the selected field; double arrows move all fields at the same time.

5	Click Next to choose fields to group on.

6	Add the database fields you want to group on to the Group By box.

When a group field is selected, you can choose a sorting order from the Sort Order box.

7	Click Next to choose fields to summarize on.

8	Add the database fields you want to total on to the Summarized Fields list.

Summarized fields apply to the group specified in the For the Group box. You can use the same fields in each of several different groups.

When a summary field is selected, you can choose a summary type for it and add a grand total.

9	Click Next to sort groups on their summarized totals.

You can select Sort All Groups, or you can specify a Top or Bottom N.

10	Click Next to create a record selection for your report.

11	Add the database fields you’ll use to filter your report to the Filtered Fields list.

Define your record selection by selecting from the box of limiting operators and entering the appropriate field value.

12	Click Next to select a report style.

13	Click Next to enter a title and saving location for your report.

14	Choose a method for previewing your report.

There are four options:

•	Preview (read only)

•	Edit with the Crystal Report Designer

•	Analyze with Crystal Analysis

•	View Later.

The Preview option opens the report in the ActiveX viewer. You can view, print, and refresh the report in the viewer.

The Edit and Analyze options open the report in the appropriate program. You can view, print, refresh, and modify the report while using all the capabilities of Crystal Reports or Crystal Analysis.

The View Later option saves the report with the name you gave it to the directory you specified.

Now that your report has been created, you can use it and modify it as you would any other report in Crystal Reports. It remains linked to your Excel spreadsheet so if you refresh the report after altering the spreadsheet, your changed data is displayed.


Working with the Microsoft Access Add-In

When you install Crystal Reports 8, the Access Add-In is added automatically. A menu option called Crystal Report Wizard is added to the Add-Ins submenu of the Tools menu in Access.

Note: Installation of the Access Add-In is not automatic for Office 97. For instructions on how to install the Crystal Report Wizard in Access 97, see Installing the Crystal Report Wizard in Access 97.

You can use the Crystal Report Wizard to create a report anytime you use Access. The option remains available until you uninstall Crystal Report Wizard in the Add-In Manager dialog box (under the Tools Menu in Access).

Note: The Access Add-In file is placed by default in the \Program Files\Seagate Software\Report Designer Component directory.


Installing the Crystal Report Wizard in Access 97

For Access 97, the Microsoft Access Add-In needs to be installed through the Access Add-In Manager before you can use it.

Once the Crystal Report Wizard is added to Access, you can select it anytime you use Access.

To install the Crystal Report Access Wizard

1	On the Tools menu in Access, point to Add-Ins and then click Add-In Manager.

The Add-Ins option is available only after a database has been opened in Access.

2	On the Add-In Manager dialog box, click Add New.

3	Search for the file Crptaccwz97.mde (for Microsoft Access 97) in the Open dialog box.

Crptaccwz97.mde is installed in the \Program Files\Seagate Software\Report Designer Component directory by default.

4	After choosing Crptaccwz97.mde, click Open.

Crystal Report Wizard is added to the available Add-Ins list in the Add-In Manager dialog box.

5	Click Close.

After installation, the Access Add-In is available from the Tools menu (the Add-Ins submenu), or from within the database dialog box.


Using the Report Creation Wizard to create a report in Access
Using the Crystal Report Creation to create a report in Access

The Crystal Report Wizard offers familiar Crystal layout and selection screens to help you create a report from your table or query.

To create a report in Access using the Report Creation Wizard

1	On the Tools menu in Access, point to Add-Ins and then click Crystal Report.

If your database table or query is open, Access warns you to save before running the wizard.

The Choose a Table or Query dialog box appears if you haven’t already highlighted a table or query in the Database dialog box or the New Report dialog box.

Another way to open the Report Creation Wizard is to select the Reports option in the Database dialog box, click New, select the Crystal Report Wizard, and then click OK. If you use this method, you can select your table or query on the New Report dialog box, and go to step 3.

2	From the Choose a Table or Query dialog box, select the table or query you want to use and click OK.

The Crystal Report Wizard appears.

3	Add the database fields you want to appear in your report to the Fields to Display list.

The arrow buttons on this dialog box enable you to move fields from one list to the other. Single arrows move only the selected field; double arrows move all fields at the same time.

4	Click Next to choose fields to group on.

5	Add the database fields you want to group on to the Group By box.

When a group field is selected, you can choose a sorting order from the Sort Order box.

6	Click Next to choose fields to summarize on.

7	Add the database fields you want to total on to the Summarized Fields list.

Summarized fields apply to the group specified in the For the Group box. You can use the same fields in each of several different groups.

When a summary field is selected, you can choose a summary type for it and add a grand total.

8	Click Next to sort groups on their summarized totals.

You can select Sort All Groups, or you can specify a Top or Bottom N.

9	Click Next to create a record selection for your report.

10	Add the database fields you’ll use to filter your report to the Filtered Fields list.

Define your record selection by selecting from the box of limiting operators and entering the appropriate field value.

11	Click Next to select a report style.

12	Click Next to enter a title and saving location for your report.

13	Choose a method for previewing your report.

There are four options:

•	Preview (read only)

•	Edit with the Report Designer

•	Analyze with Crystal Analysis

•	View Later.

The Preview option opens the report in the ActiveX viewer. You can view, print, and refresh the report in the viewer.

The Edit and Analyze options open the report in the appropriate program. You can view, print, refresh, and modify the report while using all the capabilities of Crystal Reports or Crystal Analysis.

The View Later option saves the report with the name you gave it to the directory you specified.

Now that your report has been created, you can use it and modify it as you would any other report in Crystal Reports.